1 将sname修改为username并设置字段类型为varchar(20)
alter table stu change sname username varchar(20);

2 将sname的默认值改为''
alter table stu modify username varchar(20) default '';

3 将stu表更名为user
alter table stu rename user;

4 新增字段qq 为char(15)
alter table user add qq char(15) default '';

5 删除新增的字段qq
alter table user drop qq;

6 *年龄最小的 *女同学 叫什么名子 
select sname from stu where sex =2 order by age desc limit 1;

7 *最小的 *90后 *是男生还是女生
select sex from stu where left(age,3)='199' order by age desc limit 1;

8 求男生女生的人数
select sex,count(*) from stu group by sex;

9 求每个年龄段的人数   
select left(age,3) ,count(*) from stu group by left(age,3);

10 *求男同学 每个姓氏的人数
select left(sname,1),count(*) from stu where sex =1 group by left(sname,1);

11 取人数超过2个姓氏
select left(sname,1),count(*) from stu where sex =1 group by left(sname,1) having count(*)>2;

12 创建班级表
create table class(cid smallint primary key auto_increment,cname char(30))charset utf8;

13 添加学生表字段用于与班级表关联
alter table stu add cid smallint default 0;

14 得到每个班学生数量
select cname,count(*) from stu as s ,lesson as c
where s.cid = c.cid
group by s.cid;

15 使用join完成多表关联
select * from stu as s join lesson as c 
on s.cid = c.cid;

16 *女同学 最多的 班级是
select c.cname,count(*) from stu s join lesson c 
on s.cid=c.cid where sex=2 group by s.cid
order by count(*) desc limit 1;

17 查看哪个课程没有人学
select l.cname from stu s right join lesson l  on s.cid =l.cid where s.sname is null;

18 90后 最喜欢学习的 课程是
select l.cname from stu s join lesson l 
on s.cid =l.cid
where left(age,3)='199'
group by s.cid
order by count(*) desc
limit 1;

19 女同学 最不喜欢学的课程是
select l.cname from stu s join lesson l 
on s.cid =l.cid
where sex=2
group by l.cid
order by count(*) asc
limit 1;

20 年龄最小 的女同学 在学习的课程
select l.cname from stu s join lesson l 
on s.cid =l.cid
where sex=2
order by age desc 
limit 1;

21 小明在学习哪门课程名称
select l.cname from stu s join lesson l 
on s.cid =l.cid
where s.sname='小明';

22 学习人数最少的课程是
select l.cname from stu s join lesson l 
on s.cid =l.cid
group by l.cid
order by count(*) asc
limit 1;

select l.cname from stu s join lesson l 
on s.cid =l.cid
group by l.cid
having count(*)=2;

23 学习人数超过2个人的课程是
select l.cname from stu s join lesson l 
on s.cid =l.cid
group by s.cid
having count(*)>2;

24 没有女生学习的课程是（写多条SQL)
select * from lesson where cid not in(
	select cid from stu where sex=2 group by cid
);










